﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Model;
using IDAL;
using MySqlConnector;

namespace DAL
{
    public  class MemberService
    {

        public ArrayList GetMembersByCondition(string sql)//查询社员信息
        {

            string ConnectionString = "server=localhost;database=studentclubmis;user=root ;password=123456 ;port=3306";
            MySqlConnection con = null;
            DataSet ds = new DataSet();
            ArrayList memberList = null;
            try
            {
                con = new MySqlConnection(ConnectionString);
                con.Open();
                MySqlCommand cmd = new MySqlCommand(sql, con);
                MySqlDataAdapter da = new MySqlDataAdapter();
                da.SelectCommand = cmd;
                da.Fill(ds, "members");
                if (ds.Tables["members"].Rows.Count > 0)
                {
                    memberList = new ArrayList();
                    for (int i = 0; i < ds.Tables["members"].Rows.Count; i++)
                    {
                        ClubMember member = new ClubMember();
                        DataRow row = ds.Tables["members"].Rows[i];
                        member.Studentid = row["id"].ToString();
                        member.Name = row["name"].ToString();
                        member.Sex = row["sex"].ToString();
                        member.Birthday = Convert.ToDateTime(row["birthday"]);
                        member.Grade = row["grade"].ToString();
                        member.Clubid = int.Parse(row["clubid"].ToString());
                        member.Clubname = (row["clubname"].ToString());
                        member.Departmentid = int.Parse(row["departmentid"].ToString());
                        member.Departmentname = row["departmentname"].ToString();
                        member.Professionid = int.Parse(row["professionid"].ToString());
                        member.Qq = row["qq"].ToString();
                        member.Phone = row["phone"].ToString();
                        member.Hobby = row["Hobbies"].ToString();
                        member.Memo = row["memo"].ToString();
                        member.Pic = row["picture"].ToString();
                        memberList.Add(member);


                    }
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                con.Close();
            }
            return memberList;
        }

        

        public bool AddMember(ClubMember member)//添加社员信息
        {
            string sql = " Insert into member values(@memberid,@clubid,@departmentid,@professionid,@grade,@name,@sex,@birthday,@phone,@qq,@picture,@hobbies,@memo)";
            MySqlParameter[] para = new MySqlParameter[13];
            para[0] = new MySqlParameter("@memberid", member.Studentid);
            para[1] = new MySqlParameter("@clubid", member.Clubid);
            para[2] = new MySqlParameter("@departmentid", member.Departmentid);
            para[3] = new MySqlParameter("@professionid", member.Professionid);
            para[4] = new MySqlParameter("@grade", member.Grade);
            para[5] = new MySqlParameter("@name", member.Name);
            para[6] = new MySqlParameter("@sex", member.Sex);
            para[7] = new MySqlParameter("@birthday", member.Birthday);
            para[8] = new MySqlParameter("@phone", member.Phone);
            para[9] = new MySqlParameter("@qq", member.Qq);
            para[10] = new MySqlParameter("@picture", member.Pic);
            para[11] = new MySqlParameter("@hobbies", member.Hobby);
            para[12] = new MySqlParameter("@memo", member.Memo);
            try
            {
                int n = DAL.SQLHelper.ExecuteNonQuery(sql, para);
                if (n > 0)
                    return true;
                else
                    return false;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        public bool DeleteMember(ClubMember member)//删除社员信息
        {
            string sql = "delete from member where name=@name";
            MySqlParameter[] para = new MySqlParameter[1];
            para[0] = new MySqlParameter("@name", member.Name);
            try
            {
                int n = DAL.SQLHelper.ExecuteNonQuery(sql, para);
                if (n > 0)
                    return true;
                else
                    return false;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }


        public bool UpdateMember(ClubMember member)//修改社员信息
        {
            string sql = " Update Member set clubid=@clubid,departmentid=@departmentid,professionid=@professionid,grade=@grade,name=@name,sex=@sex,birthday=@birthday,phone=@phone,qq=@qq,hobbies=@hobbies,memo=@memo where id=@memberid";
            MySqlParameter[] para = new MySqlParameter[12];
            para[0] = new MySqlParameter("@memberid", member.Studentid);
            para[1] = new MySqlParameter("@clubid", member.Clubid);
            para[2] = new MySqlParameter("@departmentid", member.Departmentid);
            para[3] = new MySqlParameter("@professionid", member.Professionid);
            para[4] = new MySqlParameter("@grade", member.Grade);
            para[5] = new MySqlParameter("@name", member.Name);
            para[6] = new MySqlParameter("@sex", member.Sex);
            para[7] = new MySqlParameter("@birthday", member.Birthday);
            para[8] = new MySqlParameter("@phone", member.Phone);
            para[9] = new MySqlParameter("@qq", member.Qq);
            para[10] = new MySqlParameter("@hobbies", member.Hobby);
            para[11] = new MySqlParameter("@memo", member.Memo);
            try
            {
                int n = SQLHelper.ExecuteNonQuery(sql, para);
                if (n > 0)
                    return true;
                else
                    return false;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        public ArrayList GetAllMember(int clubid)//查询社员信息（用社团编号）
        {
            string ConnectionString = "server=localhost;database=studentclubmis;user=root ;password=123456 ;port=3306";
            MySqlConnection con = null;
            DataSet ds = new DataSet();
            ArrayList memberList = null;
            string sql = "select * from member,club,department,attendance " +
                    "where member.clubid = club.clubid " +
                    "and member.clubid = " +clubid+
                    " and member.departmentid = department.departmentid " +
                    "and member.id = attendance.memberid";
            try
            {
                
                con = new MySqlConnection(ConnectionString);
                con.Open();
                MySqlCommand cmd = new MySqlCommand(sql, con);
                MySqlDataAdapter da = new MySqlDataAdapter();
                da.SelectCommand = cmd;
                da.Fill(ds, "members");
                if (ds.Tables["members"].Rows.Count > 0)
                {
                    memberList = new ArrayList();
                    for (int i = 0; i < ds.Tables["members"].Rows.Count; i++)
                    {
                        ClubMember member = new ClubMember();
                        DataRow row = ds.Tables["members"].Rows[i];
                        member.Studentid = row["id"].ToString();
                        member.Name = row["name"].ToString();
                        member.Sex = row["sex"].ToString();
                        member.Birthday = Convert.ToDateTime(row["birthday"]);
                        member.Grade = row["grade"].ToString();
                        member.Clubid = int.Parse(row["clubid"].ToString());
                        member.Clubname = (row["clubname"].ToString());
                        member.Departmentid = int.Parse(row["departmentid"].ToString());
                        member.Departmentname = row["departmentname"].ToString();
                        member.Professionid = int.Parse(row["professionid"].ToString());
                        member.Qq = row["qq"].ToString();
                        member.Phone = row["phone"].ToString();
                        member.Hobby = row["Hobbies"].ToString();
                        member.Memo = row["memo"].ToString();
                        member.Pic = row["picture"].ToString();
                        memberList.Add(member);


                    }
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                con.Close();
            }
            return memberList;
        }
    }
}
